package mytest.wd3;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;

public class Test_11 {
    public static void main(String[] args) {
        Long endOfDayTimestampMs = 1730530408L;
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
        Date date = new Date(endOfDayTimestampMs * 1000);
        System.out.println(date);
        String PERFORMANCE_REPORT_SQL_1 = "INSERT INTO t_user_task_call_report (\n" +
                "    cycle, division_day, user_id, user_name, user_classify, cus_cat, business_id, task_count,\n" +
                "    task_call_count, task_connect_count, call_count, connect_count,\n" +
                "    reach_count, loan_num, loan_amount, valid_flag\n" +
                ")\n" +
                "select \n" +
                " %s as cycle,\n" +
                " s.division_date as division_day,\n" +
                " s.user_gid as user_id,\n" +
                " s.username as user_name,\n" +
                " s.user_classify,\n" +
                " s.cus_cat,\n" +
                " s.business_id,\n" +
                " count(distinct s.dial_task_gid) as task_count,\n" +
                " count(DISTINCT CASE WHEN s.call_length IS NOT NULL THEN s.dial_task_gid END) AS task_call_count,\n" +
                " count(DISTINCT CASE WHEN s.call_length > 0 THEN s.dial_task_gid END) AS task_connect_count,\n" +
                " count(s.call_length) as call_count,\n" +
                " count(CASE WHEN s.call_length > 0 THEN 1 END) as connect_count,\n" +
                " count(CASE WHEN a.user_gid = t.user_id and t.dial_status = 3 THEN 1 END) as reach_count,\n" +
                " sum(CASE WHEN a.user_gid = t.user_id and t.dial_status = 3 THEN t.loan_num END) as loan_num,\n" +
                " sum(CASE WHEN a.user_gid = t.user_id and t.dial_status = 3 THEN t.loan_amount END) as loan_amount,\n" +
                " 1\n" +
                "from \n" +
                "(select\n" +
                "  a.dial_task_gid, \n" +
                "  a.user_gid, \n" +
                "  b.username, \n" +
                "  '%s' as division_date, \n" +
                "  t.user_classify,\n" +
                "  t.business_id,\n" +
                "  t.cus_cat, \n" +
                "  t.user_id,\n" +
                "  t.dial_status,\n" +
                "  t.loan_num,\n" +
                "  t.loan_amount,\n" +
                "  c.call_length\n" +
                "from (";

        String CALL_REPORT_SQL_1 ="INSERT INTO t_user_task_call_report (\n" +
                "    work_day, user_id, user_name, user_classify, cus_cat, business_id, task_count,\n" +
                "    task_call_count, task_connect_count, call_count, connect_count,\n" +
                "    connect_total_time, valid_flag\n" +
                ")\n" +
                "select \n" +
                " s.work_date as work_day, \n" +
                " s.user_gid as user_id,\n" +
                " s.username as user_name,\n" +
                " s.user_classify,\n" +
                " s.cus_cat,\n" +
                " s.business_id,\n" +
                " count(distinct s.dial_task_gid) as task_count,\n" +
                " count(DISTINCT CASE WHEN s.call_length IS NOT NULL THEN s.dial_task_gid END) AS task_call_count,\n" +
                " count(DISTINCT CASE WHEN s.call_length > 0 THEN s.dial_task_gid END) AS task_connect_count,\n" +
                " count(s.call_length) as call_count,\n" +
                " count(CASE WHEN s.call_length > 0 THEN 1 END) as connect_count,\n" +
                " IFNULL(SUM(s.call_length), 0) as connect_total_time,\n" +
                " 1\n" +
                "from \n" +
                "(select\n" +
                "  a.dial_task_gid, \n" +
                "  a.user_gid, \n" +
                "  b.username, \n" +
                "  '%s' as work_date, \n" +
                "  t.user_classify,\n" +
                "  t.business_id,\n" +
                "  t.cus_cat,\n" +
                "  c.call_length\n" +
                "from (";
        System.out.println(CALL_REPORT_SQL_1);
    }
}
